package saleMarket.dao;

import lombok.extern.slf4j.Slf4j;
import saleMarket.domain.InventoryGoods;
import saleMarket.service.InventoryGoodsService;
import saleMarket.service.ShopService;
import saleMarket.service.WarehouseService;
import util.JdbcHelper;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashSet;
import java.util.TreeSet;

/**
 * @author 张方恒
 * @version 1.0  12-13-2020
 */

@Slf4j
public class InventoryGoodsDao {
    private static InventoryGoodsDao inventoryGoodsDao = new InventoryGoodsDao();
    private InventoryGoodsDao() {}
    public static InventoryGoodsDao getInstance() {
        // 静态变量InventoryGoodsDao为空时，才创建InventoryGoodsDao对象。
        // 第二次调用本方法InventoryGoodsDao就不空了，不再创建对象。
        if (inventoryGoodsDao == null) {
            inventoryGoodsDao = new InventoryGoodsDao();
        }
        return inventoryGoodsDao;
    }

    public Collection<InventoryGoods> findAll(Connection connection) throws SQLException {
        //创建集合类对象，用来保存所有记录代表的InventoryGoods对象
        Collection<InventoryGoods> inventoryGoods = new ArrayList<>();
        //创建查询的主语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM InventoryGoods");
        //执行预编译语句，结果集放在resultSet中
        PreparedStatement pstmt = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet = pstmt.executeQuery();
        //遍历resultSet
        while(resultSet.next()){
            inventoryGoods.add(new InventoryGoods(resultSet.getInt("id"),
                    resultSet.getString("name"),
                    resultSet.getString("description"),
                    resultSet.getInt("stock"),
                    resultSet.getString("no"),
                    resultSet.getString("source"),
                    resultSet.getTimestamp("entryTime").toLocalDateTime(),
                    WarehouseService.getInstance().find(resultSet.getInt("warehouse_id")),
                    ShopService.getInstance().find(resultSet.getInt("shop_id"))
                    ));
        }
        JdbcHelper.close(resultSet,pstmt);
        return inventoryGoods;
    }

    public InventoryGoods find(int id,Connection connection) throws SQLException {

        //创建sql语句
        String find_sql = "select * from InventoryGoods where id = ?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(find_sql);
        //为预编译参数赋值
        pstmt.setInt(1,id);
        //根据id查找
        ResultSet resultSet = pstmt.executeQuery();
        resultSet.next();
        InventoryGoods inventoryGoods = new InventoryGoods(resultSet.getInt("id"),
                resultSet.getString("name"),
                resultSet.getString("description"),
                resultSet.getInt("stock"),
                resultSet.getString("no"),
                resultSet.getString("source"),
                resultSet.getTimestamp("entryTime").toLocalDateTime(),
                WarehouseService.getInstance().find(resultSet.getInt("warehouse_id")),
                ShopService.getInstance().find(resultSet.getInt("shop_id"))
        );
        JdbcHelper.close(resultSet,pstmt);
        return inventoryGoods;
    }

    public static boolean update(InventoryGoods inventoryGoods,Connection connection) throws SQLException {

                //创建sql语句
        String updateInventoryGoods_sql = "update inventoryGoods set " +
                "name=?,description=?,stock=?,no=?,source=?,warehouse_id=?,shop_id=? where id=?";
        //在该链接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(updateInventoryGoods_sql);
        //为预编译参数赋值
        pstmt.setString(1,inventoryGoods.getName());
        pstmt.setString(2,inventoryGoods.getDescription());
        pstmt.setInt(3,inventoryGoods.getStock());
        pstmt.setString(4,inventoryGoods.getNo());
        pstmt.setString(5,inventoryGoods.getSource());
        pstmt.setInt(6,inventoryGoods.getWarehouse().getId());
        pstmt.setInt(7,inventoryGoods.getShop().getId());
        pstmt.setInt(8,inventoryGoods.getId());
        //根据执行结果，具体判断所要返回的值
        if (pstmt.executeUpdate()==0){
            JdbcHelper.close(pstmt);
            return false;
        }else
            JdbcHelper.close(pstmt);
            return  true;
    }

    public boolean add(InventoryGoods inventoryGoods,Connection connection) throws SQLException {

        //创建sql语句
        String addInventoryGoods_sql =
                "insert into inventorygoods (name,description,stock,no,source,warehouse_id,shop_id) values " +
                        "(?,?,?,?,?,?,?);";
        //在该链接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(addInventoryGoods_sql);
        //为预编译参数赋值
        pstmt.setString(1,inventoryGoods.getName());
        pstmt.setString(2,inventoryGoods.getDescription());
        pstmt.setInt(3,inventoryGoods.getStock());
        pstmt.setString(4,inventoryGoods.getNo());
        pstmt.setString(5,inventoryGoods.getSource());
        pstmt.setInt(6,1);
        pstmt.setInt(7,2);
        //获取执行结果
        int affectedRowNum = pstmt.executeUpdate();
        //根据结果，具体判断所要返回的值
        if (affectedRowNum == 1) {
            JdbcHelper.close(pstmt);
            return true;
        } else
            JdbcHelper.close(pstmt);
            return false;
    }

    public boolean delete(int id,Connection connection) throws SQLException{

        //创建sql语句
        String deleteInventoryGoods_sql = "DELETE FROM InventoryGoods WHERE id=?";

        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(deleteInventoryGoods_sql);
        //为预编译参数赋值
        pstmt.setInt(1,id);
        if (pstmt.executeLargeUpdate() == 1){
            JdbcHelper.close(pstmt);
            return true;
        }else {
            JdbcHelper.close(pstmt);
            return false;
        }
    }

    public boolean buy(InventoryGoods inventoryGoods,Connection connection)
            throws SQLException{
        //创建sql语句
        String buy_sql = "update inventoryGoods set " +
                "stock=? where id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(buy_sql);
        //为预编译参数赋值
        pstmt.setInt(1,inventoryGoods.getStock());
        pstmt.setInt(2,inventoryGoods.getId());
        //根据执行结果，具体判断所要返回的值
        if (pstmt.executeUpdate()==0){
            JdbcHelper.close(pstmt);
            return false;
        }else
            JdbcHelper.close(pstmt);
        return  true;
    }
}
